/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package com.informet.ipd.modelo.dao;

import com.informet.ipd.modelo.vo.ProgEstrVO;
import com.informet.ipd.modelo.vo.ProyectosInversionVO;
import com.informet.ipd.modelo.vo.RolUsuarioVO;
import com.informet.ipd.modelo.vo.UbigeoVO;
import com.informet.ipd.modelo.vo.UsuarioVO;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import javax.swing.JOptionPane;

/**
 *
 * @author LAPTOP01
 */
public class ProgEstrDao extends DBase{


  Connection conn = null;

    public ProgEstrDao() {
        super();
    }

    public void  setOGE(ProgEstrVO bean, ResultSet rs) throws SQLException{
        bean.setCOD_OGE(rs.getInt("num_codobj_pk"));
        bean.setDESC_OGE(rs.getString("tx_descobj"));    
    }

     public void  setOEE(ProgEstrVO bean, ResultSet rs) throws SQLException{
       bean.setCOD_OEE(rs.getInt("num_codobjesp_pk"));
        bean.setDESC_OEE(rs.getString("tx_descobjesp"));
    }

    public void  setActividades(ProgEstrVO bean, ResultSet rs) throws SQLException{
       bean.setCOD_ACTIVIDAD(rs.getInt("num_codactper_pk"));
       bean.setDESC_ACTIVIDA(rs.getString("tx_descactper"));
    }

    public void  setANIOS(ProgEstrVO bean, ResultSet rs) throws SQLException{
       bean.setCOD_ANIO(rs.getInt("num_codanio_pk"));
       bean.setDESC_ANIO(rs.getString("tx_descanio"));
    }

    public void  setListTableTituProg(ProgEstrVO bean, ResultSet rs) throws SQLException{
           bean.setTituProgTab(rs.getString("tx_tituloprg"));
           bean.setJustiProgTab(rs.getString("tx_justiprg"));
           bean.setCodPrograma(rs.getInt("num_codprg_pk"));

           bean.setCOD_OGE(rs.getInt("num_codobj"));
           bean.setCOD_OEE(rs.getInt("num_codobjesp_pk"));
           bean.setCOD_ACTIVIDAD(rs.getInt("num_codactper_pk"));          
           bean.setFECHA_INICIO(rs.getString("dt_iniprg"));
           bean.setFECHA_FIN(rs.getString("dt_terprg"));
           bean.setRUTA(rs.getString("tx_rutaprg"));
           bean.setCOD_ANIO(rs.getInt("num_anioprg"));
           bean.setMONTO(rs.getBigDecimal("num_presuprg"));

    }

      public void  setListRegion(UbigeoVO bean, ResultSet rs) throws SQLException{
          bean.setNUM_CODDPTO_PK(rs.getString("NUM_CODDPTO_PK"));
          bean.setTX_NOMDPTO(rs.getString("TX_NOMDPTO"));
          
    }



        public void  setComboProvincia(UbigeoVO bean, ResultSet rs) throws SQLException{
          bean.setNUM_CODPRV_PK(rs.getString("NUM_CODPRV_PK"));
          bean.setTX_NOMPRV(rs.getString("TX_NOMPRV"));

    }
     public void  setComboDistrito(UbigeoVO bean, ResultSet rs) throws SQLException{
          bean.setNUM_CODDIST_PK(rs.getString("NUM_CODDIST_PK"));
          bean.setTX_NOMDIST(rs.getString("TX_NOMDIST"));
      
    }

     public void  setComboUgel(UbigeoVO bean, ResultSet rs) throws SQLException{
          bean.setNUM_CODUGEL_PK(rs.getString("NUM_CODUGEL_PK"));
          bean.setTX_NOMBUGEL(rs.getString("TX_NOMBUGEL"));

    }



     public List getlistOGE() throws SQLException {

        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<ProgEstrVO> lista = new ArrayList<ProgEstrVO>(0);
        //List lista=null;
        //lista=null;
        String sql = "";
        try {
            conn = DriverManager.getConnection(url, usuario, password);
            stmt = conn.createStatement();
            sql = "select num_codobj_pk,tx_descobj from me_obj";
            rs = stmt.executeQuery(sql);
            ProgEstrVO bean = null;
            while (rs.next()) {
                bean = new ProgEstrVO();
               setOGE(bean, rs);

               lista.add(bean);

            }

        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }
        return lista;
    }

    public List getlistOEE(int codOGE) throws SQLException {

        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<ProgEstrVO> lista = new ArrayList<ProgEstrVO>(0);
        //lista=null;
        String sql = "";
        try {
            conn = DriverManager.getConnection(url, usuario, password);
            stmt = conn.createStatement();
            sql = "select num_codobjesp_pk,tx_descobjesp from me_objesp where num_codobj_pk="+codOGE;
            rs = stmt.executeQuery(sql);
            ProgEstrVO bean = null;
            while (rs.next()) {
                bean = new ProgEstrVO();
                setOEE(bean, rs);
                lista.add(bean);
            }
        }
        catch(Exception e){
           JOptionPane.showMessageDialog(null, "listaapermanente: "+e.toString());

        }
        
        finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }
        return lista;
    }


       public List getlistActividades(int codOEE) throws SQLException {

        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<ProgEstrVO> lista = new ArrayList<ProgEstrVO>(0);
        //lista=null;
        String sql = "";
        try {
            conn = DriverManager.getConnection(url, usuario, password);
            stmt = conn.createStatement();
            sql = "select num_codactper_pk,tx_descactper from me_actper where num_codobjesp_pk="+codOEE;
            rs = stmt.executeQuery(sql);
            ProgEstrVO bean = null;
            while (rs.next()) {
                bean = new ProgEstrVO();
                setActividades(bean,rs);
                lista.add(bean);
            }

        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }
        return lista;
    }

        public List getlistAnios() throws SQLException {

        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<ProgEstrVO> lista = new ArrayList<ProgEstrVO>(0);
        //lista=null;
        String sql = "";
        try {
            conn = DriverManager.getConnection(url, usuario, password);
            stmt = conn.createStatement();
            sql = "select num_codanio_pk,tx_descanio from me_aniopresuprog where num_estado=1";
            rs = stmt.executeQuery(sql);
            ProgEstrVO bean = null;
            while (rs.next()) {
                bean = new ProgEstrVO();
                setANIOS(bean,rs);
                lista.add(bean);
            }

        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }
        return lista;
    }

      public String spMan(ProgEstrVO bean) {
        String msj = "";
        //int codP=bean.getCodPrograma();
        try {
        	
            conn = DriverManager.getConnection(url, usuario, password);
            CallableStatement cs = conn.prepareCall("{call Man_insertProgEstr(?,?,?,?,?,?,?,?,?,?,?)}");
            //codActividad, codAnio, titu_programa,justificacion,fechaInicio, fechaFin,monto
            cs.setInt(1, bean.getCOD_ACTIVIDAD());
            cs.setInt(2, bean.getCOD_ANIO());
            cs.setString(3, bean.getTITULO_PROGRAMA());
            cs.setString(4, bean.getDESC_JUSTIFICACION());
            cs.setString(5, bean.getFECHA_INICIO());
            cs.setString(6, bean.getFECHA_FIN()); 
            cs.setBigDecimal(7, bean.getMONTO());
            cs.setString(8, bean.getADJ());
            cs.setString(9, bean.getRUTA());
            cs.setString(10, bean.getTipoOper());
            cs.setInt(11, bean.getCodProg());
            cs.execute();
        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, e.toString());
            msj = e.toString();
            return msj;
        }
        return msj;
    }

     public List getListTituProgForTable()  throws SQLException {
        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<ProgEstrVO> lista = new ArrayList<ProgEstrVO>(0);
        //lista=null;
        String sql = "";
        try {
            conn = DriverManager.getConnection(url, usuario, password);
            stmt = conn.createStatement();
            //sql = "select * from me_programa";
            //////
                 sql = " select "+
                           " pp.num_anioprg_pk as num_anioprg,pp.num_presuprg , o.num_codobj_pk as num_codobj,os.num_codobjesp_pk , a.num_codactper_pk, p.NUM_CODPRG_PK, p.TX_TITULOPRG,p.TX_JUSTIPRG,convert(varchar,p.DT_INIPRG,103) as DT_INIPRG,convert(varchar,p.DT_TERPRG,103) as DT_TERPRG,p.TX_ADJUPRG,p.TX_RUTAPRG,p.NUM_CODACTPER_PK "+
                           " from me_programa p inner join me_actper a on p.num_codactper_pk=a.num_codactper_pk "+
                           "		   inner join me_objesp os on os.num_codobjesp_pk=a.num_codobjesp_pk "+
			"			   inner join me_obj o on o.num_codobj_pk=os.num_codobj_pk "+
			"			   inner join me_presuprg pp on pp.num_codprg_pk=p.num_codprg_pk ";

            ////
            rs = stmt.executeQuery(sql);
            ProgEstrVO bean = null;
            while (rs.next()) {
                bean = new ProgEstrVO();
                setListTableTituProg(bean,rs);
                lista.add(bean);
            }

        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }
        return lista;
    }



      public List getRegion()  throws SQLException {
        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<UbigeoVO> lista = new ArrayList<UbigeoVO>(0);
        //lista=null;
        String sql = "";
        try {
            conn = DriverManager.getConnection(url, usuario, password);
            stmt = conn.createStatement();
            //sql = "select * from me_programa";
            //////
                 sql = "SELECT  NUM_CODDPTO_PK,TX_NOMDPTO FROM ME_DEPARTAMENTO";

            ////
            rs = stmt.executeQuery(sql);
            UbigeoVO bean = null;
            while (rs.next()) {
                bean = new UbigeoVO();
                setListRegion(bean,rs);
                lista.add(bean);
            }

        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }
        
        return lista;
    }


      public List getComboProvincia(String id) throws SQLException {
        PreparedStatement pstmt = null;
        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<UbigeoVO> lista = new ArrayList<UbigeoVO>(0);
        //lista=null;
        String sql = "";
        try {
            conn = DriverManager.getConnection(url, usuario, password);

            sql = "SELECT  NUM_CODPRV_PK,TX_NOMPRV FROM ME_PROVINCIA WHERE NUM_CODDPTO_PK=?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, id);
            rs = pstmt.executeQuery();
            UbigeoVO bean = null;
            while (rs.next()) {
                bean = new UbigeoVO();
                setComboProvincia(bean, rs);
                lista.add(bean);
            }
            rs.close();
            pstmt.close();
        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }
        return lista;
    }

public List getComboDistrito(String id) throws SQLException {
    
        PreparedStatement pstmt = null;
        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<UbigeoVO> lista = new ArrayList<UbigeoVO>(0);
        //lista=null;
        String sql = "";
        try {
            conn = DriverManager.getConnection(url, usuario, password);

            sql = "SELECT  NUM_CODDIST_PK,TX_NOMDIST FROM ME_DISTRITO WHERE NUM_CODPRV_PK=?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, id);
            rs = pstmt.executeQuery();
            UbigeoVO bean = null;
            while (rs.next()) {
                bean = new UbigeoVO();
                setComboDistrito(bean, rs);
                lista.add(bean);
            }
            rs.close();
            pstmt.close();
        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }
     //  JOptionPane.showMessageDialog(null, lista.size());
        return lista;
    }


    public List getComboUgel(String id) throws SQLException {

        PreparedStatement pstmt = null;
        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<UbigeoVO> lista = new ArrayList<UbigeoVO>(0);
        //lista=null;
        String sql = "";
        try {
            conn = DriverManager.getConnection(url, usuario, password);

            sql = "SELECT  * FROM ME_UGEL WHERE NUM_CODDIST_PK=?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, id);
            rs = pstmt.executeQuery();
            UbigeoVO bean = null;
            while (rs.next()) {
                bean = new UbigeoVO();
                setComboUgel(bean, rs);
                lista.add(bean);
            }
            rs.close();
            pstmt.close();
        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }
      // JOptionPane.showMessageDialog(null, lista.size());
        return lista;
    }


}
